ajDBCreateRecord function

Available since AlchemyJ v4.1

Description

The ajDBCreateRecord function is used for creating records in a database table. Please take note that to run this function in Excel, you would need to set up the Data Source Connection in ##ExternalResources.

Syntax

ajDBCreateRecord( table_name, column_headers, data, [table_schema], [return_type], [transpose], [include_header], [data_source_id], [convert_to_text], [run_condition],[run_by_function_point_only] )

Argument Name	Argument Type	Description
table_name (required)	String	The name of the table where records will be created.
column_headers (required)	Range / Array	The columns to be updated. It can be a range of single row or column where each cell is a table column name or a data name defined in DB Schema. CLOB and BLOB are not supported.
data (required)	Range / Array	The range where data is located. The orientation can either be landscape or portrait. The orientation should be the same as column_headers.
table_schema (optional)	Range / Array	The range that defines the DB Schema. The full range of DB Schema table should be included.
return_type (optional)	Double	The return type of the function. 0 - The record that is just created. Only Out or InOut specified in the 'In Out' column of DB Schema would be included. This return type only works in the PostgreSQL database. 1 - The record that is just created. Only Out or InOut specified in the 'In Out' column of DB Schema would be included. This return type only works in the Oracle database. 2 - Number of affected records. The default value is 2.
transpose (optional)	Boolean	If it equals FALSE, the return records will not be transposed. If it equals TRUE, the return records will be transposed from row to column. The default value is FALSE.
include_header (optional)	Boolean	If it equals TRUE, the return result will include the column headers as the first row. This only applies when ‘return_type’ is 0 or 1. If it equals FALSE, the return result will contain data only. The default value is FALSE.
data_source_id (optional)	String	The data source shall be used in this database operation. It shall be defined in ##ExternalResources worksheet. The default value is "primary".
convert_to_text (optional)	Boolean	Convert all values into text or not.
run_condition (optional)	Boolean	The function will run when the value is TRUE. Otherwise, it will not run. The default value is TRUE.
run_by_function_point_only (optional)	Boolean	If it equals FALSE, the function can be executed through ‘Excel Calculation’ (can be either Automatic or Manual, Calculate Now or Calculate Sheet) or Preview Function Point. If it equals TRUE, the function can be executed with Preview Function Point (AlchemyJ ribbon \ Preview Function Point) only. The default value is TRUE.

The function will return:

1) Return Value: Number of affected records or the record just created.

2) Return Type: Single Value / Multiple values (array formula)

Example

Before using the function, you need to set up the Data Source Connection in ##ExternalResources worksheet.

Before running this function point, you need to configure the Data Source ID in ##RestEndpointGroup worksheet or ##JavaApiClass.

We will use the following table in our examples. The table name is TB_CUSTOMER.

Example 1 - Return Type is 2

This example creates one record in tb_customer. It returns the number of record affected as return type is omitted (default value is 2).

Check the result from database.

Example 2 - Return Type is 1(Only support Oracle database)

This example creates a record in tb_customer and return record column value with return_type is 1. When return_type is 1, table_schema need to be defined and it only works in the Oracle database. It will return a set of records' columns defines in DB Schema with the In Out column set as Out or InOut in DB Schema would be returned.

Click Add Component to and select DB Schema to add the DB Schema.

Define the In Out type in DB Schema.

In this example, the CUSTOMER_NAME and CUSTOMER_LVLcolumns value are returned since it's In Out type is InOut in DB Schema.

Check the result from database.

Click here to download the use case workbooks for further reference.

You can also check the video help from Youtube!

Error Scenarios

It will return #VALUE! when missing any required parameter or mismatch parameter type. Besides, system will raise error for below scenario(s).

Error Scenario
DB connection error.
Table name is empty.
Table name does not exist in provided DB Schema.
The table schema columns are invalid.
The table schema range does not include a header or a row of data.
The provided column header cannot be found in the specified table schema range.
Number of columns in column_headers does not match with the number of data columns.
The Mandatory field defined in table_schema is empty.
Invalid return_type, it must be 1 or 2 for Oracle.
Invalid return_type, it must be 0 or 2 for PostgreSQL.
Invalid return_type, it must be 2 for MySQL or MSSQL.
Invalid return_type, it must be 2 when In Out column in DB schema is not defined.